Setting the Spatial Index Dimension to 3D

To properly query a 3D urban model feature, the Oracle Spatial index must be set to 3D by defining sdo_indx_dims=3.

For example:

CREATE INDEX Building_SIDX ON BUILDING(OGC_GEOMETRY) INDEXTYPE
IS MDSYS.SPATIAL_INDEX_V2 PARAMETERS('layer_gtype=MULTIPOLYGON, sdo_indx_dims=3');

Setting the Appropriate gtype

To store 3D models in Oracle, the appropriate gtype needs to be set in the index as shown in the script below.

The script below shows how to define a table to be used to store a 3D building feature and domain table in Oracle Spatial:

-- create_BUILDING.sql
--
-- creates the spatially enabled BUILDING table. For use with Oracle and Bentley Map.

-- Create feature table (BUILDING
--
CREATE TABLE BUILDING(
  ID        NUMBER CONSTRAINT BUILDING_pk PRIMARY KEY,
  Owner     VARCHAR2(50),
  Name      VARCHAR2(50),
  Status    VARCHAR2(50),
  geometry  SDO_GEOMETRY);

-- Create table for STATUS domain

--
CREATE TABLE DOMAIN_BUILDING_STATUS
("STATUS" VARCHAR2(10) NOT NULL,
CONSTRAINT "DOMAIN_BUILDING_STATUS_PK" PRIMARY KEY("STATUS"));


Insert into DOMAIN_BUILDING_STATUS("STATUS") values ('Proposed');
Insert into DOMAIN_BUILDING_STATUS("STATUS") values ('Review');
Insert into DOMAIN_BUILDING_STATUS("STATUS") values ('Existing');
Insert into DOMAIN_BUILDING_STATUS("STATUS") values ('Abandoned');
Insert into DOMAIN_BUILDING_STATUS("STATUS") values ('Demolished');


-- Create foreign key


alter table building
add constraint DOMAIN_BUILDING_STATUS_FK
foreign key(STATUS)
references DOMAIN_BUILDING_STATUS(STATUS)
disable;
--
-- Add metadata to spatial view USER_SDO_GEOM_METADATA.
--
INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
  VALUES ('BUILDING', 'GEOMETRY',
    MDSYS.SDO_DIM_ARRAY
      (MDSYS.SDO_DIM_ELEMENT('X', 310630.031200000, 314262.608400001, 0.000000050),
       MDSYS.SDO_DIM_ELEMENT('Y', 4832370.993600001, 4835182.257600000, 0.000000050),
       MDSYS.SDO_DIM_ELEMENT('Z', -1000, 1000, 0.000000050)),NULL);
--
-- Note: to work properly in Bentley Map, the SDO_GTYPE needs to
-- be explicitly defined in the index.
--
CREATE INDEX BUILDING_idx ON BUILDING(geometry)
  INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2
    PARAMETERS ('layer_gtype=MULTIPOLYGON sdo_indx_dims=3');
commit;
exit;
--
-- end of create_BUILDING.sql

Supported Oracle Spatial Index Dimensions

Note 1) Z-value not used in query.

Note 2) Queries in non-top views use the view rectangle as the top of the frustum and the bottom is calculated using the Oracle metadata extents for the layers